
/****** Object:  StoredProcedure [dbo].[SFDA_authority_staffFilesSelect]    Script Date: 07/27/2013 18:24:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[SFDA_authority_staffFilesSelect]  
@beginId int,  
@reportsToId int  
as  
set nocount on  
  
if object_id('tmpdb_#tmp1') is not null drop table #tmp1  
create table #tmp1(id int,idResportTo int,staffId int)  
  
if object_id('tmpdb_#tmp2') is not null drop table #tmp2  
create table #tmp2(id int,idResportTo int,staffId int)  
  
--insert into #tmp1(id,idResportTo,staffId)  
--select id,idResportTo,staffId from t_treestaff where id = @beginId  
--insert into #tmp2(id,idResportTo,staffId) 
--select id,idResportTo,staffId from t_treestaff where staffId = @reportsToId 

DECLARE @PARENT INT,@CHILD INT,@COUNT INT
SET @COUNT =0
SET @CHILD =@reportsToId
	WHILE @CHILD<>0  
	BEGIN
	insert into #tmp2(id,idResportTo,staffId)  
	select id,idResportTo,staffId from t_treestaff where staffId = @CHILD
	
	SELECT @CHILD= idResportTo  from t_treestaff where staffId = @CHILD
	
    END
    IF (SELECT COUNT(*) FROM #tmp2 WHERE staffId=@beginId)>0
    BEGIN
    SET @COUNT=1
	END
	--select * from #tmp1  
--select * from #tmp2  
  ELSE
  BEGIN
	update t_treestaff set idResportTo =@reportsToId where staffId=@beginId--(select staffId from #tmp2 staffId) where staffId = @beginId  
  END
select @COUNT as result

GO

